package Util::Tuan::TuansSearch;
use Moose;
use namespace::autoclean;
use JSON;

has 'schema' => (
	is => 'rw',
	required => 1,
	isa => 'DBIx::Class::Schema',
);

sub get_best_tuan {
        my ($self, $university_id) = @_;
        my $curr_time = `date --iso-8601=seconds`;
        my @tuan = $self->schema->resultset('Tuan')->search(
                        {'me.status' => 'Y',
			'me.tihuo_time_to' => {'>' => $curr_time},
                        'university_tuan.university_id' => $university_id},
                        {
                                '+select'=> [
                                        {count => 'user_tuan.user_id', -as => 'buyer_count'}
                                ],
				order_by => {-desc => [qw/buyer_count/]},
                                group_by => 'me.id',
                                join => ['user_tuan', 'university_tuan'],
				rows => 2,
				page => 1
                        }
                );
        return \@tuan;
}

sub find_tuans_in_university {
        my ($self, $university_id) = @_;
        my $curr_time = `date --iso-8601=seconds`;
        my @tuan = $self->schema->resultset('Tuan')->search(
                        {'me.status' => 'Y',
			'me.tihuo_time_to' => {'>' => $curr_time},
                        'university_tuan.university_id' => $university_id},
                        {
                                '+select'=> [
                                        {count => 'user_tuan.user_id', -as => 'buyer_count'}
                                ],
				order_by => {-desc => [qw/buyer_count/]},
                                group_by => 'me.id',
                                join => ['user_tuan', 'university_tuan']
                        }
                );
        return \@tuan;
}

sub get_ad_tuans {
        my ($self) = @_;
	my @tuans = $self->schema->resultset('Tuan')->search(
		{'me.is_index' => 'Y',
		},
		{
			'+select'=> [
				{sum => 'user_tuan_stores.amount', -as => 'total_sold_count'}
			],
			order_by => {-desc => [qw/total_sold_count/]},
			group_by => 'me.id',
			join => 'user_tuan_stores'
		}
	);

	return @tuans;	
}

sub get_tuijian_tuans {
        my ($self) = @_;
	my $curr_time = `date --iso-8601=seconds`;
	my @tuans = $self->schema->resultset('Tuan')->search(
		{'me.status' => 'Y',
		'me.tihuo_time_to' => {'<' => $curr_time}
		},
		{
			'+select'=> [
				{count => 'user_tuan.user_id', -as => 'buyer_count'}
			],
			order_by => {-desc => [qw/buyer_count/]},
			group_by => 'me.id',
			rows => 8,
			page => 1,
			join => 'user_tuan'
		}
	);
	return \@tuans;	
}

sub get_tuan_on_page {
        my ($self, $page, $rows) = @_;
	my $curr_time = `date --iso-8601=seconds`;
	my @tuans;
        unless(defined($page)) {
                @tuans = $self->schema->resultset('Tuan')->search(
                        {'me.status' => 'Y',
			'me.tihuo_time_to' => {'>' => $curr_time},
			'me.tihuo_time_from' => {'<' => $curr_time}
			},
                        {
				'+select'=> [
					{count => 'user_tuan.user_id', -as => 'buyer_count'}
				],
                                order_by => {-desc => [qw/me.created/]},
				group_by => 'me.id',
				join => 'user_tuan'
                        }
                );
        } else {
                @tuans = $self->schema->resultset('Tuan')->search(
                        {'me.status' => 'Y',
			'me.tihuo_time_to' => {'>' => $curr_time},
			'me.tihuo_time_from' => {'<' => $curr_time}
			},
                        {
				'+select'=> [
					{count => 'user_tuan.user_id', -as => 'buyer_count'}
				],
                                order_by => {-desc => [qw/me.created/]},
                                rows => $rows,
                                page => $page,
				group_by => 'me.id',
				join => 'user_tuan'
                        }
                );
        }
	if(scalar(@tuans) < $rows) {
		$rows = $rows - scalar(@tuans);
                my @extra = $self->schema->resultset('Tuan')->search(
                        {'me.status' => 'Y',
			'me.tihuo_time_to' => {'<' => $curr_time}
			},
                        {
				'+select'=> [
					{count => 'user_tuan.user_id', -as => 'buyer_count'}
				],
                                order_by => {-desc => [qw/me.tihuo_time_to/]},
                                rows => $rows,
                                page => $page,
				group_by => 'me.id',
				join => 'user_tuan'
                        }
                );
		@tuans = (@tuans, @extra);
	}
	return @tuans;
}

sub get_tuan_total_sold_on_page {
        my ($self, $page, $rows) = @_;
	my $curr_time = `date --iso-8601=seconds`;
	my @tuans;
        unless(defined($page)) {
                @tuans = $self->schema->resultset('Tuan')->search(
                        {'me.status' => 'Y',
			'me.tihuo_time_to' => {'>' => $curr_time},
			'me.tihuo_time_from' => {'<' => $curr_time}
			},
                        {
				'+select'=> [
					{sum => 'user_tuan_stores.amount', -as => 'total_sold_count'}
				],
                                order_by => {-desc => [qw/me.created/]},
				group_by => 'me.id',
				join => 'user_tuan_stores'
                        }
                );
        } else {
                @tuans = $self->schema->resultset('Tuan')->search(
                        {'me.status' => 'Y',
			'me.tihuo_time_to' => {'>' => $curr_time},
			'me.tihuo_time_from' => {'<' => $curr_time}
			},
                        {
				'+select'=> [
					{sum => 'user_tuan_stores.amount', -as => 'total_sold_count'}
				],
                                order_by => {-desc => [qw/me.created/]},
                                rows => $rows,
                                page => $page,
				group_by => 'me.id',
				join => 'user_tuan_stores'
                        }
                );
        }
	if(scalar(@tuans) < $rows) {
		$rows = $rows - scalar(@tuans);
                my @extra = $self->schema->resultset('Tuan')->search(
                        {'me.status' => 'Y',
			'me.tihuo_time_to' => {'<' => $curr_time}
			},
                        {
				'+select'=> [
					{sum => 'user_tuan_stores.amount', -as => 'total_sold_count'}
				],
                                order_by => {-desc => [qw/me.tihuo_time_to/]},
                                rows => $rows,
                                page => $page,
				group_by => 'me.id',
				join => 'user_tuan_stores'
                        }
                );
		@tuans = (@tuans, @extra);
	}
	return @tuans;
}

sub get_tuans_by_user {
	my ($self, $user_id, $role) = @_;
	return $self->schema->resultset('Tuan')->search(
			{'users.id' => $user_id},
			{
				join => {'tuan_stores' => {'stores' => {'user_stores' => 'users'}}} ,
				'me.role' => $role
			}
		);
	
}

sub find_ongoing_tuans_for_university {
	my ($self, $university_id) = @_;
	my @tuans;
	my $curr_time = `date --iso-8601=seconds`;
	@tuans = $self->schema->resultset('Tuan')->search(
                        {'me.status' => 'Y',
			'me.tihuo_time_to' => {'>' => $curr_time},
			'me.tihuo_time_from' => {'<' => $curr_time},
			'university_tuan.university_id' => $university_id},
			{
				'+select'=> [
					{count => 'user_tuan.user_id', -as => 'buyer_count'}
				],
                                order_by => {-desc => [qw/me.created/]},
				group_by => 'me.id',
				join => ['user_tuan', 'university_tuan']
			}
		);
	return \@tuans;
} 

sub find_ongoing_stores_for_university {
	my ($self, $page, $university_id, $category_id) = @_;
	my $curr_time = `date --iso-8601=seconds`;
	my @stores;
	my $rows = 30;
	if($category_id ne 'null') {
		@stores = $self->schema->resultset('Store')->search(
				{'tuan.status' => 'Y',
				'tuan.tihuo_time_to' => {'>' => $curr_time},
				'tuan.tihuo_time_from' => {'<' => $curr_time},
				'stores_category.category_id' => $category_id,
				'university_tuan.university_id' => $university_id},
				{
					'+select'=> [
						'tuan.id',
						{sum => 'user_tuan_stores.amount', -as => 'total_sold_count'}
					],
					'+as' => [
						'tuan_id',
						'total_sold_count'
					],
					order_by => {-desc => [qw/total_sold_count/]},
					group_by => 'me.id',
					rows => $rows,
					page => $page,			
					join => [{'tuan_stores' => {'tuan' =>  'university_tuan'}}, 'user_tuan_stores', 'stores_category']
				}
			);
	} else {
		@stores = $self->schema->resultset('Store')->search(
				{'tuan.status' => 'Y',
				'tuan.tihuo_time_to' => {'>' => $curr_time},
				'tuan.tihuo_time_from' => {'<' => $curr_time},
				'university_tuan.university_id' => $university_id},
				{
					'+select'=> [
						'tuan.id',
						{sum => 'user_tuan_stores.amount', -as => 'total_sold_count'}
					],
					'+as' => [
						'tuan_id',
						'total_sold_count'
					],
					order_by => {-desc => [qw/total_sold_count/]},
					group_by => 'me.id',
					rows => $rows,
					page => $page,			
					join => [{'tuan_stores' => {'tuan' =>  'university_tuan'}}, 'user_tuan_stores', 'stores_category']
				}
			);
	}
	return @stores;
} 

sub find_ongoing_stores_count_for_university {
	my ($self, $university_id, $category_id) = @_;
	my $curr_time = `date --iso-8601=seconds`;
	my $count = 0;
	if ($university_id ne 'null' and $category_id ne 'null') {
		$count = $self->schema->resultset('Store')->search(
				{'tuan.status' => 'Y',
				'tuan.tihuo_time_to' => {'>' => $curr_time},
				'tuan.tihuo_time_from' => {'<' => $curr_time},
				'university_tuan.university_id' => $university_id,
				'stores_category.category_id' => $category_id},
				{
					group_by => 'me.id',
					join => [{'tuan_stores' => {'tuan' =>  'university_tuan'}}, 'stores_category']
				}
			)->count;
	} elsif($category_id eq 'null') {
		$count = $self->schema->resultset('Store')->search(
				{'tuan.status' => 'Y',
				'tuan.tihuo_time_to' => {'>' => $curr_time},
				'tuan.tihuo_time_from' => {'<' => $curr_time},
				'university_tuan.university_id' => $university_id},
				{
					group_by => 'me.id',
					join => {'tuan_stores' => {'tuan' =>  'university_tuan'}}
				}
			)->count;

	}
	return $count;
} 

sub find_ongoing_tuans_total_sold_for_university {
	my ($self, $university_id) = @_;
	my @tuans;
	my $curr_time = `date --iso-8601=seconds`;
	@tuans = $self->schema->resultset('Tuan')->search(
                        {'me.status' => 'Y',
			'me.tihuo_time_to' => {'>' => $curr_time},
			'me.tihuo_time_from' => {'<' => $curr_time},
			'university_tuan.university_id' => $university_id},
			{
				'+select'=> [
					{sum => 'user_tuan_stores.amount', -as => 'total_sold_count'}
				],
                                order_by => {-desc => [qw/total_sold_count/]},
				group_by => 'me.id',
				join => ['user_tuan_stores', 'university_tuan']
			}
		);
	return \@tuans;
} 

sub find_toopen_tuans_for_university {
	my ($self, $university_id) = @_;
	my @tuans;
	my $curr_time = `date --iso-8601=seconds`;
	@tuans = $self->schema->resultset('Tuan')->search(
                        {'me.status' => 'Y',
			'me.tihuo_time_from' => {'>' => $curr_time},
			'university_tuan.university_id' => $university_id},
			{
				'+select'=> [
					{count => 'user_tuan.user_id', -as => 'buyer_count'}
				],
                                order_by => {-desc => [qw/me.created/]},
				group_by => 'me.id',
				join => ['user_tuan', 'university_tuan']
			}
		);
	return \@tuans;
} 
sub find_finished_tuans_for_university {
	my ($self, $university_id) = @_;
	my @tuans;
	my $curr_time = `date --iso-8601=seconds`;
	@tuans = $self->schema->resultset('Tuan')->search(
                        {'me.status' => 'Y',
			'me.tihuo_time_to' => {'<' => $curr_time},
			'university_tuan.university_id' => $university_id},
			{
				'+select'=> [
					{count => 'user_tuan.user_id', -as => 'buyer_count'}
				],
                                order_by => {-desc => [qw/me.created/]},
				group_by => 'me.id',
				rows => 3,
				page => 1,
				join => ['user_tuan', 'university_tuan']
			}
		);
	return \@tuans;
} 
sub find_shenpi_tuans_for_university {
	my ($self, $university_id) = @_;
	my @tuans;
	@tuans = $self->schema->resultset('Tuan')->search(
                        {'me.status' => 'A',
			'university_tuan.university_id' => $university_id},
			{
				join => 'university_tuan'
			}
		);
	return \@tuans;
} 
sub find_tuans_for_user {
	my ($self, $user_id, $page) = @_;
	my @tuans;
	@tuans = $self->schema->resultset('Tuan')->search(
			{'user_tuan.user_id' => $user_id},
			{
				select => [
					'id',
					'name',
					'created',
					'image',
					'user_tuan.role',
					'status',
					'tihuo_time_from',
					'tihuo_time_to',
					{count => 'user_tuan_2.id', -as => 'user_count'}
				],
				as => [qw/
					id
					name
					created	
					image
					role
					status
					tihuo_time_from
					tihuo_time_to
					user_count
				/],
				order_by=>{-desc=>"user_tuan.created"},
				group_by=>[qw/id/],
				page=>$page,
				join => ['user_tuan', 'user_tuan']
			}
		);
	return @tuans;
} 

sub find_tuans_owned_by_user {
	my ($self, $user_id) = @_;
	my @tuans;
	@tuans = $self->schema->resultset('Tuan')->search(
			{'user_tuan.user_id' => $user_id,'user_tuan.role' => 'C'},
			{
				select => [
					'id',
					'name',
					'created',
					'image',
					'user_tuan.role',
					'status',
					'tihuo_time_from',
					'tihuo_time_to',
					{count => 'user_tuan_2.id', -as => 'user_count'}
				],
				as => [qw/
					id
					name
					created	
					image
					role
					status
					tihuo_time_from
					tihuo_time_to
					user_count
				/],
				order_by=>{-desc=>"user_tuan.created"},
				group_by=>[qw/id/],
				join => ['user_tuan', 'user_tuan'],
				distinct => 'user_tuan.tuan_id'
			}
		);
	return @tuans;
} 

sub find_tuans_for_user_count { my ($self, $user_id) = @_;
	return $self->schema->resultset('Tuan')->search(
			{'user_tuan.user_id' => $user_id},
			{
				join => ['user_tuan']
			}
		)->count();
	
}

sub tuan_search {
	my ($self, $universities, $zhuangtai, $start_index, $end_index) = @_;
	my %zhuangtaihash = map{$_ => 1}@$zhuangtai;
	my $curr_time = `date --iso-8601=seconds`;
	my @tuans_all;
	if(exists($zhuangtaihash{'A'})) {
		my @tuans = $self->schema->resultset('Tuan')->search(
			{ 'me.status' => 'A',
			'university_tuan.university_id' => {'in' => $universities}},
			{
                                join => 'university_tuan',
				distinct => 'me.id',
				 order_by => {-desc=>"created"}
                        }
		);
		push @tuans_all, @tuans;
	}

	if(exists($zhuangtaihash{'W'})) {
		my @tuans = $self->schema->resultset('Tuan')->search(
			{ 'me.status' => 'Y',
			'me.tihuo_time_from' => {'>' => $curr_time},
			'university_tuan.university_id' => {'in' => $universities}},
			{
                                join => 'university_tuan',
				order_by => {-desc=>"created"},
				distinct => 'me.id'
                        }
		);
		push @tuans_all, @tuans;
	}

	if(exists($zhuangtaihash{'P'})) {
		my @tuans  = $self->schema->resultset('Tuan')->search(
			{ 'me.status' => 'Y',
			'me.tihuo_time_to' => {'>' => $curr_time},
			'me.tihuo_time_from' => {'<' => $curr_time},
			'university_tuan.university_id' => {'in' => $universities}},
			{
                                join => 'university_tuan',
				order_by => {-desc=>"created"},
				distinct => 'me.id'
                        }
		);
		push @tuans_all, @tuans;
	}

	if(exists($zhuangtaihash{'N'})) {
		my @tuans = $self->schema->resultset('Tuan')->search(
			{ 'me.status' => 'N',
			'university_tuan.university_id' => {'in' => $universities}},
			{
                                join => 'university_tuan',
				order_by => {-desc=>"created"},
				distinct => 'me.id'
                        }
		);
		push @tuans_all, @tuans;
	}

	if(exists($zhuangtaihash{'J'})) {
		my @tuans = $self->schema->resultset('Tuan')->search(
			{ 'me.status' => 'Y',
			'me.tihuo_time_to' => {'<' => $curr_time},
			'university_tuan.university_id' => {'in' => $universities}},
			{
                                join => 'university_tuan',
				order_by => {-desc=>"created"},
				distinct => 'me.id'
                        }
		);
		push @tuans_all, @tuans;
	}
	my $length = scalar @tuans_all;
	my @arr;
	if($length <= $end_index ) {
		$length = $length - 1;
		@arr = @tuans_all[$start_index..$length];
	} else {
		@arr = @tuans_all[$start_index..$end_index];
	}
	return \@arr;

}

sub get_names_in_json {
	my ($self, $name) = @_;
	$name = "%" . $name . "%";
        my @arr = $self->schema->resultset('Tuan')->search({name => { like => $name}}, {columns => [qw/name/]});
        my %result;
        my $i = 0;
        foreach (@arr){
                my $value = $_->get_column("name");
                $value =~ s/^\s+//;
                $value =~ s/\s+$//;
                $result{$value} = 1 unless (exists $result{$value});
        }
        my @keys = keys %result;
        return to_json(\@keys);
}

sub find {
	my ($self, $tuan_id) = @_;
	return $self->schema->resultset('Tuan')->find($tuan_id);
}

sub add_to_user_tuan {
	my ($self, $tuan_id, $user_id, $user_name, $tuan_name, $status, $join_quantity, $role) = @_;

	my $tuan = $self->find($tuan_id);
	my $curr_time = `date +%F`;
        $tuan->add_to_user_tuan({
                user_id => $user_id,
                user_name => $user_name,
                tuan_id => $tuan_id,
                tuan_name => $tuan_name,
                status => $status,
                join_quantity => $join_quantity,
                join_time=> $curr_time,
                role=>$role}
	);
}

sub get_total_tuan_number_by_name {
        my ($self, $name) = @_;
	$name =~ s/^\s+//;
        $name =~ s/\s+$//;
        $name = "%" . $name . "%";
	return $self->schema->resultset('Tuan')->search(
			{ name => { like => $name}},
			{}
		)->count;
}
sub tuan_search_by_name {
	my ($self, $name, $page) = @_;
	$name =~ s/^\s+//;
        $name =~ s/\s+$//;
        $name = "%" . $name . "%";
	my @tuans = $self->schema->resultset('Tuan')->search(
			{ name => { like => $name}},
			{ page=> $page, rows=>30, order_by => {-desc=>"created"}}
		);
	return \@tuans;
}

sub get_total_tuan_number_no_args {
	my ($self) = @_;
        my $result = $self->schema->resultset('Tuan')->search()->count;
	return $result;
}

sub get_total_tuan_number {
	my ($self, $universities, $zhuangtai) = @_;
	my %zhuangtaihash = map{$_ => 1}@$zhuangtai;
	my $curr_time = `date --iso-8601=seconds`;
	my $result = 0;
	if(exists($zhuangtaihash{'A'})) {
		my $count = $self->schema->resultset('Tuan')->search(
			{ 'me.status' => 'A',
			'university_tuan.university_id' => {'in' => $universities}},
			{
                                join => 'university_tuan',
				distinct => 'me.id'
                        }
		)->count;
		$result = $result + $count;
	}

	if(exists($zhuangtaihash{'Z'})) {
		my $count = $self->schema->resultset('Tuan')->search(
			{ 'me.status' => 'Y',
			'me.zhifu_eff_to' => {'>' => $curr_time},
			'university_tuan.university_id' => {'in' => $universities}},
			{
                                join => 'university_tuan',
				distinct => 'me.id'
                        }
		)->count;
		$result = $result + $count;
	}

	if(exists($zhuangtaihash{'F'})) {
		my $count  = $self->schema->resultset('Tuan')->search(
			{ 'me.status' => 'Y',
			'me.zhifu_eff_to' => {'<' => $curr_time},
			'me.fahuo_eff_to' => {'>' => $curr_time},
			'university_tuan.university_id' => {'in' => $universities}},
			{
                                join => 'university_tuan',
				distinct => 'me.id'
                        }
		)->count;
		$result = $result + $count;
	}

	if(exists($zhuangtaihash{'N'})) {
		my $count = $self->schema->resultset('Tuan')->search(
			{ 'me.status' => 'N',
			'university_tuan.university_id' => {'in' => $universities}},
			{
                                join => 'university_tuan',
				distinct => 'me.id'
                        }
		)->count;
		$result = $result + $count;
	}

	if(exists($zhuangtaihash{'J'})) {
		my $count = $self->schema->resultset('Tuan')->search(
			{ 'me.status' => 'Y',
			'me.fahuo_eff_to' => {'<' => $curr_time},
			'university_tuan.university_id' => {'in' => $universities}},
			{
                                join => 'university_tuan',
				distinct => 'me.id'
                        }
		)->count;
		$result = $result + $count;
	}
	return $result;
}

sub get_time_out {
	my ($self, $tuan_id, $status) = @_;
	unless ($tuan_id eq '') {
                my $my_tuan = $self->schema->resultset('Tuan')->find($tuan_id);
		if($status eq 'W') {
			#TODO change to kaituan_time later
			return $my_tuan->tihuo_time_from;
		}
		if($status eq 'P'){
			#TODO change to jietuan_time later
			return $my_tuan->tihuo_time_to;
		}
        } else {
                return '0';
        }

}



__PACKAGE__->meta->make_immutable;

1;
